﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class mrj_MrjControler : System.Web.UI.Page
{
    public string UserId
    {
        get
        {
            return Session["UserID"].ToString();
        }
    }
    MyDbHelper mydb = new MyDbHelper();
    protected void Page_Load(object sender, EventArgs e)
    {
        string aciton = Request.Params["aciton"];
        switch (aciton)
        {
            case "pos_sale_chart":
                GetChartsData();
                break;
            case "pos_sale_chart2":
                GetChartsData2();
                break;
            case "pos_shopsale_chart":
                pos_shopsale_chart();
                break;
            case "pos_shopsale_chart_outexcel":
                BtnOutExcel();
                break;
            case "lodshoplist":
                lodshoplist();
                break;
            case "mrj_week_data":
                getweekdata();
                break;
            case "mrj_time_data":
                gettimedata();
                break;
            case "mrj_year_data":
                getyeardata();
                break;
            case "mrj_peaktime_data":
                GetPeakTimedata();
                break;
            case "mrj_13week_data":
                Get13Weekdata();
                break;
            case "get_city_list":
                Get_city_list();
                break;
        }
    }

    /// <summary>
    /// 周流量API
    /// </summary>
    private void getweekdata()
    {
        object result = null;
        try
        {
            DataSet ds = mydb.DM_GetDateSet("exec KM_Mrj_KeekHB '" + this.UserId+"'");
            string weekname = "";
            string week1 = "";
            string week2 = "";
            string clum1 = ds.Tables[0].Columns[2].ColumnName;
            string clum2 = ds.Tables[0].Columns[3].ColumnName;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                weekname += "\""+dr["weekname"] + "\",";
                week1 += dr[clum1] + ",";
                week2 += dr[clum2] + ",";
            }
            string sbr = "{\"data\":{\"legend\":[\"" + clum1 + "\",\"" + clum2 + "\"],\"category\":[" + weekname.TrimEnd(',') + "],"+
                          "\"series\": [{\"name\": \""+clum1+"\",\"type\": \"line\", \"data\": ["+week1.TrimEnd(',')+"]},"+
                          "{\"name\": \""+clum2+"\",\"type\": \"line\", \"data\": ["+week2.TrimEnd(',')+"]}]"+ "}}";
            Response.Write(sbr);
            Response.End();
            return;
        }
        catch
        {
            result = new { status = -1, msg = "数据库执行出错！" };
        }
        Response.Write(JsonHelper.SerializeObject(result));
        Response.End();
    }
    /// <summary>
    /// 时间段API
    /// </summary>
    private void gettimedata()
    {
        string bdate = Request.Params["bdate"];
        if (string.IsNullOrWhiteSpace(bdate))
        {
            bdate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
        }
        object result = null;
        try
        {
            DataSet ds = mydb.DM_GetDateSet("exec KM_Mrj_TimeTB '"+bdate+"','" + this.UserId + "'");
            string TimeStr = "";
            string qty1 = "";
            string qty2 = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                TimeStr += "\"" + dr["TimeStr"] + "点\",";
                qty1 += dr["qty"] + ",";
                qty2 += dr["qty2"] + ",";
            }
            string sbr = "{\"data\":{\"category\":[" + TimeStr.TrimEnd(',') + "]," +
                          "\"series\": [{\"name\": \"本期\",\"type\": \"line\", \"data\": [" + qty1.TrimEnd(',') + "]}," +
                          "{\"name\": \"上周同期\",\"type\": \"line\", \"data\": [" + qty2.TrimEnd(',') + "]}]" + "}}";
            Response.Write(sbr);
            Response.End();
            return;
        }
        catch
        {
            result = new { status = -1, msg = "数据库执行出错！" };
        }
        Response.Write(JsonHelper.SerializeObject(result));
        Response.End();
    }
    /// <summary>
    /// 月客流API
    /// </summary>
    private void getyeardata()
    {
        string bdate = Request.Params["bdate"];
        if (string.IsNullOrWhiteSpace(bdate))
        {
            bdate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
        }
        object result = null;
        try
        {
            DataSet ds = mydb.DM_GetDateSet("exec KM_Mrj_MonthTB '" + bdate + "','" + this.UserId + "'");
            string TimeStr = "";
            string qty1 = "";
            string qty2 = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                TimeStr += "\"" + dr["TimeStr"] + "月\",";
                qty1 += dr["qty"] + ",";
                qty2 += dr["qty2"] + ",";
            }
            string sbr = "{\"data\":{\"category\":[" + TimeStr.TrimEnd(',') + "]," +
                          "\"series\": [{\"name\": \"今年\",\"type\": \"line\", \"data\": [" + qty1.TrimEnd(',') + "]}," +
                          "{\"name\": \"去年\",\"type\": \"line\", \"data\": [" + qty2.TrimEnd(',') + "]}]" + "}}";
            Response.Write(sbr);
            Response.End();
            return;
        }
        catch
        {
            result = new { status = -1, msg = "数据库执行出错！" };
        }
        Response.Write(JsonHelper.SerializeObject(result));
        Response.End();
    }
    /// <summary>
    /// 当日时间段API
    /// </summary>
    private void GetPeakTimedata()
    {
        string bdate = Request.Params["bdate"];
        if (string.IsNullOrWhiteSpace(bdate))
        {
            bdate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
        }
        object result = null;
        try
        {
            DataSet ds = mydb.DM_GetDateSet("exec KM_Mrj_PeakTime '" + bdate + "','" + this.UserId + "'");
            string TimeStr = "";
            string qty1 = "";
            string datalist = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                TimeStr += "\"" + dr["TimeStr"] + "\",";
                qty1 += dr["qty"] + ",";
                datalist += "{\"value\"" + ":\"" + dr["qty"] + "\",\"name\"" + ":\"" + dr["TimeStr"] + "\"},";
            }
            string sbr = "{\"data\":{\"category\":[" + TimeStr.TrimEnd(',') + "]," +
                          "\"series\": [" + qty1.TrimEnd(',') + "],\"datalist\":["+datalist.TrimEnd(',')+"]}" +
                          "}";
            Response.Write(sbr);
            Response.End();
            return;
        }
        catch
        {
            result = new { status = -1, msg = "数据库执行出错！" };
        }
        Response.Write(JsonHelper.SerializeObject(result));
        Response.End();
    }
    /// <summary>
    /// 近13周客流API
    /// </summary>
    private void Get13Weekdata()
    {
        object result = null;
        string shopid = Request.Params["shopid"];
        //if (string.IsNullOrWhiteSpace(shopid))
        //{
        //    result = new { status = -1, msg = "店铺参数不能为空！" };
        //    Response.Write(JsonHelper.SerializeObject(result));
        //    Response.End();
        //    return;
        //}
        try
        {
            DataSet ds = mydb.DM_GetDateSet("exec KM_Mrj_13WeekData '" + shopid + "','" + this.UserId + "'");
            string weeknum = "";
            string qty1 = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                weeknum += "\"第" + dr["weeknum"] + "周\",";
                qty1 += dr["qty"] + ",";
            }
            string sbr = "{\"data\":{\"category\":[" + weeknum.TrimEnd(',') + "]," +
                          "\"series\": [" + qty1.TrimEnd(',') + "]}" +
                          "}";
            Response.Write(sbr);
            Response.End();
            return;
        }
        catch
        {
            result = new { status = -1, msg = "数据库执行出错！" };
        }
        Response.Write(JsonHelper.SerializeObject(result));
        Response.End();
    }

    /// <summary>
    /// 获取店铺流量对比分析数据集
    /// </summary>
    /// <returns></returns>
    private DataSet KM_MrjShopSaleKpi_Rpt(int num)
    {
        try
        {
            string shopid = Request.Params["shopid"];
            string bdate = Request.Params["bdate"];
            string edate = Request.Params["edate"];
            string val = " ";
            if (!string.IsNullOrEmpty(shopid))
            {
                val += "(";
                string[] list = shopid.Split(',');
                foreach (string s in list)
                {
                    val += "''" + s + "'',";
                }
                val = val.TrimEnd(',');
                val += ")";
            }

            if (num == 0)
            {
                DataSet ds = mydb.DM_GetDateSet("KM_MrjShopSaleKpi_Rpt '" + bdate + "','" + edate + "','" + val + "'");
                return ds;
            }
            else //导出
            {
                DataSet ds = mydb.DM_GetDateSet("KM_MrjShopSaleKpi_Rpt_OutExcel '" + bdate + "','" + edate + "','" + val + "'");
                return ds;
            }
        }
        catch (Exception e)
        {
            throw (e);
        }
    }
    private void pos_shopsale_chart()
    {
        try
        {
            DataSet ds = KM_MrjShopSaleKpi_Rpt(0);
            /*
             * {"kcmc":{"categories":["北京路店","武汉店","长春店","佛山店"]},
                "score":[{"data":[75,84,90,96,86,100],"name":"本期销售额"},{"data":[83,87,88,91,83,100],"name":"目标销售额"}]}
             * */
            string sbr = "";
            string sbr2 = "";
            string timeStr = "";
            string moneyStr = "";
            string counstr = "";
            string kinqty = "";
            string zhlStr = "";
            string kdj = "";
            string ldv = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                timeStr += "\"" + dr["shopname"] + "\",";
                moneyStr += dr["saleamount"].ToString() + ",";
                counstr += dr["salecount"].ToString() + ",";
                kinqty += dr["kinqty"].ToString() + ",";
                zhlStr += dr["zhl"].ToString() + ",";
                kdj += dr["salekdj"].ToString() + ",";
                ldv += dr["saleldv"].ToString() + ",";
                sbr2 += "{\"shopname\":\"" + dr["shopname"]
                        + "\",\"saleamount\":\"" + dr["saleamount"]
                        + "\",\"saleqty\":\"" + dr["saleqty"]
                        + "\",\"salecount\":\"" + dr["salecount"]
                        + "\",\"kinqty\":\"" + dr["kinqty"]
                        + "\",\"zhl\":\"" + dr["zhl"]
                        + "\",\"salekdj\":\"" + dr["salekdj"]
                        + "\",\"saleldv\":\"" + dr["saleldv"]
                        + "\",\"salejdj\":\"" + dr["salejdj"]
                        + "\",\"kddate\":\"" + dr["kddate"]
                            + "\"},";
            }
            sbr = "{\"timelist\":{\"categories\":[" + timeStr.TrimEnd(',') + "],labels: {rotation: -20 }}," +
            "\"series\":[{\"data\":[" + moneyStr.TrimEnd(',') + "],\"name\":\"销售额（百元）\"},{\"data\":["
            + counstr.TrimEnd(',') + "],\"name\":\"销售单数（单）\"},{\"data\":[" + zhlStr.TrimEnd(',')
            + "],\"name\":\"转换率（%）\"},{\"data\":[" + kinqty.TrimEnd(',') + "],\"name\":\"客流量\"},{\"data\":["
            + kdj.TrimEnd(',') + "],\"name\":\"客单价（元）\"},{\"data\":["
            + ldv.TrimEnd(',') + "],\"name\":\"连带率\"}],\"datalist\":[" + sbr2 + "]}";
            Response.Write(sbr);//返回礼品列表
            Response.End();
            return;
        }
        catch (Exception ex)
        {
            Activity.Log.Error("请求报表数据异常：" + ex);
        }
    }
    /// <summary>
    /// 导出店铺流量对比分析数据
    /// </summary>
    private void BtnOutExcel()
    {
        //导出EXCEL
        string strJson = string.Empty;
        try
        {
            string mid = Request.Params["mid"];
            if (!string.IsNullOrWhiteSpace(mid))
            {
                DataSet ds = KM_MrjShopSaleKpi_Rpt(1);
                string path = NpoiHelper.ExportByWeb(mid, ds, "店铺流量对比分析");
                strJson = "{\"status\":\"1\",\"msg\":\"成功\",\"data\" :\"" + path + "\" }";
                //this.ResourceManager1.AddScript("outExcle('" + path + "')");
            }
            else
            {
                strJson = "{\"status\":\"0\",\"msg\":\"mid为空\"}";
            }
        }
        catch (Exception ex)
        {
            Activity.Log.Error("请求报表数据异常：" + ex);
            strJson = "{\"status\":\"0\",\"msg\":\"" + ex.Message + "！\"}";
        }
        Response.Write(strJson);
        Response.End();
    }

    private void Get_city_list()
    {
        string loadType = Request.Params["LoadType"];
        string Param = Request.Params["Param"];
        string sql="";
        string sbr = "";
        if (loadType == "0")
            sql = "select distinct BigAreaName as name from V_UserShopDataRights where userid = " + this.UserId;
        else if (loadType == "1")
            sql = "select distinct AreaName as name from V_UserShopDataRights where userid = " + this.UserId + " and BigAreaName ='" + Param + "'";

        DataSet ds = mydb.DM_GetDateSet(sql);
        try
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                string list = "[";
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    list += "{\"name\" : \""+dr["name"]+"\"},";
                }
                list = list.TrimEnd(',') + "]";
                sbr = "{\"status\":\"1\",\"msg\":\"成功\",\"data\" :" + list + " }";
            }
            else
            {
                sbr = "{\"status\":\"0\",\"msg\":\"没有数据！\"}";
            }
        }
        catch(Exception ex)
        {
            sbr = "{\"status\":\"-1\",\"msg\":\"" + ex.Message + "！\"}";
        }
        Response.Write(sbr);
        Response.End();
    }
    private void lodshoplist()
    {
        string shopname = Request.Params["shopname"];
        string bigAreaName = Request.Params["BigAreaname"];
        string areaName = Request.Params["AreaName"];
        string str = "";
        if (shopname != "")
        {
            str += " and mrjshopname like '%" + shopname + "%'";
        }
        if (!string.IsNullOrWhiteSpace(bigAreaName) && bigAreaName !="0")
        {
            str += string.Format(" and BigAreaName='{0}'", bigAreaName);
        }
        if (!string.IsNullOrWhiteSpace(areaName) && areaName != "0")
        {
            str += string.Format(" and AreaName='{0}'", areaName);
        }

        DataSet ds = mydb.DM_GetDateSet(@"select kmshopid,mrjshopname from Bas_mrj_Shop s
        inner join V_UserShopDataRights b on b.shopid = s.kmshopid where 1=1 and userid="+Session["UserID"]+" " + str + " order by mrjshopid");
        if (ds.Tables[0].Rows.Count > 0)
        {
            string sbr = "";
            sbr = "[";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                sbr += "{\"shopname\":\"" + dr["mrjshopname"]
                    + "\",\"shopid\":\"" + dr["kmshopid"]
                    + "\"},";
            }
            sbr = sbr.TrimEnd(',') + "]";
            Response.Write(sbr);
            Response.End();
        }
        else
        {
            Response.Write("NO_DATA");
            Response.End();
        }

    }
    private void GetChartsData()
    {
        string shopid =Request.Params["shopid"];
        string bdate = Request.Params["bdate"];
        string edate = Request.Params["edate"];
        string datetype = Request.Params["stype"];
        string val = " ";
        if (!string.IsNullOrEmpty(shopid))
        {
            val += "(";
            string[] list = shopid.Split(';');
            foreach (string s in list)
            {
                val += "''" + s + "'',";
            }
            val = val.TrimEnd(',');
            val += ")";
        }

        DataSet ds = mydb.DM_GetDateSet("KM_MrjSaleKpi_Rpt '" + bdate + "','"+edate+"','" + shopid + "','" + datetype + "'");
        /*
         * {"kcmc":{"categories":["北京路店","武汉店","长春店","佛山店"]},
            "score":[{"data":[75,84,90,96,86,100],"name":"本期销售额"},{"data":[83,87,88,91,83,100],"name":"目标销售额"}]}
         * */
        string sbr = "";
        string sbr2 = "";
        string timeStr = "";
        string moneyStr = "";
        string counstr = "";
        string kinqty = "";
        string zhlStr = "";
        string kdj = "";
        string ldv = "";
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            timeStr += "\"" + dr["bdate"] + "\",";
            moneyStr += dr["saleamount"].ToString() + ",";
            counstr += dr["salecount"].ToString() + ",";
            kinqty += dr["kinqty"].ToString() + ",";
            zhlStr += dr["zhl"].ToString() + ",";
            kdj += dr["salekdj"].ToString() + ",";
            ldv += dr["saleldv"].ToString() + ",";
            sbr2 += "{\"bdate\":\"" + dr["bdate"]
                    + "\",\"saleamount\":\"" + dr["saleamount"]
                    + "\",\"saleqty\":\"" + dr["saleqty"]
                    + "\",\"salecount\":\"" + dr["salecount"]
                    + "\",\"kinqty\":\"" + dr["kinqty"]
                    + "\",\"zhl\":\"" + dr["zhl"]
                    + "\",\"salekdj\":\"" + dr["salekdj"]
                    + "\",\"saleldv\":\"" + dr["saleldv"]
                    + "\",\"salejdj\":\"" + dr["salejdj"]
                        + "\"},";
        }
        sbr = "{\"timelist\":{\"categories\":[" + timeStr.TrimEnd(',') + "],labels: {rotation: 0 }}," +
        "\"series\":[{\"data\":[" + moneyStr.TrimEnd(',') + "],\"name\":\"销售额（百元）\"},{\"data\":["
        + counstr.TrimEnd(',') + "],\"name\":\"销售单数（单）\"},{\"data\":[" + zhlStr.TrimEnd(',')
        + "],\"name\":\"转换率（%）\"},{\"data\":[" + kinqty.TrimEnd(',') + "],\"name\":\"客流量\"},{\"data\":[" 
        + kdj.TrimEnd(',') + "],\"name\":\"客单价（元）\"},{\"data\":[" 
        + ldv.TrimEnd(',') + "],\"name\":\"连带率\"}],\"datalist\":["+sbr2+"]}";
        Response.Write(sbr);//返回礼品列表
        Response.End();
        return;
    }
    private void GetChartsData2()
    {
        string shopid = Request.Params["shopid"];
        string bdate = Request.Params["bdate"];
        string edate = Request.Params["edate"];
        string datetype = Request.Params["stype"];
        string pjType = Request.Params["pjType"];
        string val = " ";
        if (!string.IsNullOrEmpty(shopid))
        {
            val += "(";
            string[] list = shopid.Split(';');
            foreach (string s in list)
            {
                val += "''" + s + "'',";
            }
            val = val.TrimEnd(',');
            val += ")";
        }

        DataSet ds = mydb.DM_GetDateSet("KM_MrjSaleKpi_Rpt '" + bdate + "','" + edate + "','" + shopid + "','" + datetype + "'," + pjType);
        /*
         * {"kcmc":{"categories":["北京路店","武汉店","长春店","佛山店"]},
            "score":[{"data":[75,84,90,96,86,100],"name":"本期销售额"},{"data":[83,87,88,91,83,100],"name":"目标销售额"}]}
         * */
        string sbr = "";
        string sbr2 = "";
        string timeStr = "";
        string moneyStr = "";
        string counstr = "";
        string kinqty = "";
        string zhlStr = "";
        string kdj = "";
        string ldv = "";
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            string saledate = dr["bdate"].ToString();
            if(datetype=="0")
                saledate = saledate.Substring(10);

            timeStr += "\"" + saledate + "\",";
            moneyStr += dr["saleamount"].ToString() + ",";
            counstr += dr["salecount"].ToString() + ",";
            kinqty += dr["kinqty"].ToString() + ",";
            zhlStr += dr["zhl"].ToString() + ",";
            kdj += dr["salekdj"].ToString() + ",";
            ldv += dr["saleldv"].ToString() + ",";
            sbr2 += "{\"bdate\":\"" + saledate
                    + "\",\"saleamount\":\"" + dr["saleamount"]
                    + "\",\"saleqty\":\"" + dr["saleqty"]
                    + "\",\"salecount\":\"" + dr["salecount"]
                    + "\",\"kinqty\":\"" + dr["kinqty"]
                    + "\",\"zhl\":\"" + dr["zhl"]
                    + "\",\"salekdj\":\"" + dr["salekdj"]
                    + "\",\"saleldv\":\"" + dr["saleldv"]
                    + "\",\"salejdj\":\"" + dr["salejdj"]
                        + "\"},";
        }
        sbr = "{\"timelist\":{\"categories\":[" + timeStr.TrimEnd(',') + "]}," +
        "\"series\":[{\"data\":[" + moneyStr.TrimEnd(',') + "],\"name\":\"销售额（百元）\",\"type\":\"line\"},{\"data\":["
        + counstr.TrimEnd(',') + "],\"name\":\"销售单数（单）\",\"type\":\"line\"},{\"data\":[" + zhlStr.TrimEnd(',')
        + "],\"name\":\"转换率（%）\",\"type\":\"line\"},{\"data\":[" + kinqty.TrimEnd(',') + "],\"name\":\"客流量\",\"type\":\"line\"},{\"data\":["
        + kdj.TrimEnd(',') + "],\"name\":\"客单价（元）\",\"type\":\"line\"},{\"data\":["
        + ldv.TrimEnd(',') + "],\"name\":\"连带率\",\"type\":\"line\"}],\"datalist\":[" + sbr2.TrimEnd(',') + "]}";
        Response.Write(sbr);//返回礼品列表
        Response.End();
        return;
    }
}
